package jp.co.mclnet.sample;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;

public class Sample4POI {

    private static final int DAYS_OF_YEAR = 365;

    private static final String INTEGER_FORMAT = "^[0-9]+[.][0]";

    private static final String PIRIOD = "\\.";

    private static final int START_YEAR = 1900;

    protected static Date convertSerialValue(int serialValue) {
        int day = serialValue;
        if (60 <= day) {
            day--;
        }
        int leapYearCount = 0;
        for (int i = 0; i < day / DAYS_OF_YEAR; i++) {
            if (isLeapYear(START_YEAR + i)) {
                leapYearCount++;
            }
        }
        day -= leapYearCount;
        int year = START_YEAR + (day / DAYS_OF_YEAR);
        day %= DAYS_OF_YEAR;
        int[] lastDays = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
        if (isLeapYear(year)) {
            lastDays[1]++;
        }
        int month;
        for (month = 0; day >= lastDays[month]; month++) {
            day -= lastDays[month];
        }
        Calendar calendar = Calendar.getInstance();
        calendar.set(year, month, day, 0, 0, 0);
        return calendar.getTime();
    }

    protected static Object getValue(Cell cell) {
        CellValue cellValue = cell.getSheet().getWorkbook().getCreationHelper()
                .createFormulaEvaluator().evaluate(cell);

        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                int numberValue = (int) cellValue.getNumberValue();
                return convertSerialValue(numberValue);
            }
            double num = cellValue.getNumberValue();
            String numStr = String.valueOf(num);
            if (numStr.matches(INTEGER_FORMAT)) {
                return Integer.valueOf(numStr.split(PIRIOD)[0]);
            } else {
                return Double.valueOf(numStr);
            }
        case Cell.CELL_TYPE_STRING:
            return cellValue.getStringValue();
        case Cell.CELL_TYPE_BLANK:
            return null;
        case Cell.CELL_TYPE_BOOLEAN:
            return new Boolean(cellValue.getBooleanValue());
        case Cell.CELL_TYPE_ERROR:
            return ErrorEval.getText(cellValue.getErrorValue());
        }
        return null;
    }

    protected static boolean isLeapYear(int year) {
        return ((year % 4 == 0) && (year % 100 != 0 || year % 400 == 0));
    }

    public static void main(String[] args) {
        String filePath = "deprecated/xls/speed.xls";
        InputStream is = null;
        try {
            try {
                is = new FileInputStream(filePath);
            } catch (FileNotFoundException e) {
                throw new IllegalArgumentException(e);
            }
            is = new BufferedInputStream(is);
            POIFSFileSystem pfs = new POIFSFileSystem(is);
            HSSFWorkbook book = new HSSFWorkbook(pfs);
            for (int z = 0; z < book.getNumberOfSheets(); z++) {
                HSSFSheet sheet = book.getSheetAt(z);
                int firstRowNum = sheet.getFirstRowNum();
                int lastRowNum = sheet.getLastRowNum();
                for (int y = firstRowNum; y <= lastRowNum; y++) {
                    HSSFRow row = (HSSFRow) sheet.getRow(y);
                    if (row == null) {
                        continue;
                    }
                    short firstCellNum = row.getFirstCellNum();
                    short lastCellNum = row.getLastCellNum();
                    for (short x = firstCellNum; x <= lastCellNum; x++) {
                        HSSFCell cell = (HSSFCell) row.getCell((int) x);
                        if (cell == null) {
                            continue;
                        }
                        Object value = getValue(cell);
                        System.out.print(value);
                        System.out.print("[");
                        System.out.print(value.getClass().getName());
                        System.out.println("]");
                    }
                }
            }
        } catch (IOException e) {
            throw new IllegalArgumentException(e);
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    throw new IllegalStateException(e);
                }
            }
        }
    }
}
